Mysql 常用命令

发表于 更新于 分类于

Mysql 常用命令

查询表结构信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
**表结构，字段类型，主键，是否为空等属性**
desc tabl_name;

**查询注释信息**
select * from information_schema.
where table_schema = 'db'  #表所在数据库
and table_name = 'tablename' ; #你要查的表

**表中字段详细信息**
desc tableName; #简要信息
show full columns from tableName; #详细信息

**表中有多少条数据**
select count(*) from tableName;
select count(*) from tableName where gender=‘NULL’；#按条件查看，查看某个字段
查看表生成的DDL
1
show create table table_name;
Mysql 新建用户及赋权
1
2
3
4
5
6
7
8
9
10
**创建用户**
CREATE USER 'operator'@'%' IDENTIFIED BY 'password';
**删除用户**
DROP USER 'username'@'host';

**授权**
GRANT privileges ON databasename.tablename TO 'username'@'host'
**例子**
GRANT SELECT, INSERT ON test.user TO 'operator'@'%';
GRANT ALL ON *.* TO 'operator'@'%';
修改密码
1
2
alter user 'operator'@'localhost' IDENTIFIED BY 'password'；
flush privileges;

###

查询用户详细信息
1
2
3
use mysql;

select user,authentication_string,host from user;

Postgresql常用命令

基础操作

查询数据库、表大小信息

1
select pg_size_pretty(pg_database_size('prometheus'));    #查看数据库大小

创建只读用户

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Create a group
CREATE ROLE readaccess;

-- Grant access to existing tables
GRANT USAGE ON SCHEMA public TO readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;

-- Grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;

-- Create a final user with password
CREATE USER read_only WITH PASSWORD 'passw0rd';
GRANT readaccess TO  read_only;

创建用户及数据库

1
2
3
4
5
6
create role grafana with login password '1qaz@PASSW0RD'；
create database grafana_server owner grafana;   # 创建数据库指定所属用户
select * from information_schema.schemata;    # 查看数据库schema
create schema grafana_schema;    # 创建schema
grant all on schema grafana_schema to grafana；   # 授权用户可以访问的schema
grant select on schema grafana_schema to grafana；   # 只授权查询权限